﻿using CKS2320.Common.Model;
using CKS2320.Domain.DBEntity;
using ICSharpCode.SharpZipLib.Zip;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming.Values;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Security.Policy;
using System.Text;
using System.Text.RegularExpressions;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace CKS2320.Common.Utils
{
    public static class FileUtils
    {
        /// <summary>
        /// 删除文件（非正常删除：删除一个文件夹下除当前使用文件外的所有文件）
        /// </summary>
        /// <param name="directoryPath">文件夹路径</param>
        /// <param name="LocalFilePath">当前正在使用的文件</param>
        public static void DeleteDirectoryOther(string directoryPath, string LocalFilePath)
        {
            try
            {
                for (int i = 0; i < Directory.GetFiles(directoryPath).ToList().Count; i++)
                {
                    var file = Directory.GetFiles(directoryPath)[i];
                    if (file != LocalFilePath)
                        System.IO.File.Delete(file);
                }
            }
            catch (Exception ex)
            {
                //LogHelper.Info("删除文件出错：" + ex.Message);
            }
        }

        /// <summary>
        /// 删除文件（正常删除）
        /// </summary>
        /// <param name="directoryPath">文件夹路径</param>
        /// <param name="fileName">要删除的文件名</param>
        public static void DeleteDirectory(string directoryPath, string fileName)
        {
            try
            {
                for (int i = 0; i < Directory.GetFiles(directoryPath).ToList().Count; i++)
                {
                    var file = Directory.GetFiles(directoryPath)[i];
                    if (file == fileName)
                        System.IO.File.Delete(file);
                }
            }
            catch (Exception ex)
            {
                //LogHelper.Info("删除文件出错：" + ex.Message);
            }
        }
    }

    /// <summary>
    /// 文件操作类
    /// </summary>
    public class FileOperation : ControllerBase
    {
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="sheetName"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public ResultReturn FileExport<T>(List<T> dto, string sheetName, string fileName)
        {
            List<ReturnKeyValueList> listInfo = null;
            if (dto is List<ReturnKeyValueList>)
                listInfo = dto as List<ReturnKeyValueList>;
            else
                listInfo = ModelUtils.ReturnTransfor(dto);

            if (listInfo != null)
            {
                try
                {
                    //创建workbook对象
                    //XSSFWorkbook xSSFworkbook = new XSSFWorkbook();
                    HSSFWorkbook hSSFworkbook = new HSSFWorkbook();
                    //创建工作表
                    NPOI.SS.UserModel.ISheet sheet = hSSFworkbook.CreateSheet(sheetName);
                    //在工作表中添加一行
                    NPOI.SS.UserModel.IRow rows = sheet.CreateRow(0);
                    //标题
                    var firstOne = listInfo.FirstOrDefault();
                    if (firstOne != null)
                    {
                        int IndexTitle = 0;
                        foreach (var item in firstOne.values)
                        {
                            rows.CreateCell(IndexTitle).SetCellValue(item.Name);
                            IndexTitle++;
                        }
                    }
                    //内容
                    int i = 0;
                    foreach (var item in listInfo)
                    {
                        NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
                        if (item != null)
                        {
                            int IndexCell = 0;
                            foreach (var itemValue in item.values)
                            {
                                row.CreateCell(IndexCell).SetCellValue(itemValue.Value);
                                IndexCell++;
                            }
                        }
                        i++;
                    }
                    byte[] data = null;
                    using (MemoryStream ms = new MemoryStream())
                    {
                        hSSFworkbook.Write(ms);
                        ms.Flush();
                        data = ms.ToArray();
                    }
                    Stream stream = new MemoryStream(data);
                    FileStreamResult fsr = File(stream, "application/vnd.ms-excel", fileName + ".xls");
                    return MessageInfoUtils.Success(fsr);
                }
                catch (Exception ex)
                {
                    return MessageInfoUtils.Fail("Error：" + ex.Message);
                }
            }
            else
            {
                return MessageInfoUtils.Fail("No Data To Display!");
            }
        }

        public ResultReturn FileExportresuft(List<ExportQueryModel> list, List<ExportQueryCount> listCount, List<PerformanceRecord> ExportRecordList, List<Equipmentfailurehistory> ExportAlarmList, string sheetName, string fileName)
        {
            try
            {
                byte[] data = null;
                string excelFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ExportQuery.xls");
                string newFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ExportQuery.xlsx");
                if (System.IO.File.Exists(excelFilePath))
                {
                    using (FileStream fileStream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
                    {
                        HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
                        ISheet sheetop10 = workbook.GetSheet("总表");
                        list = list.OrderBy(p => p.No).ToList();
                        int rows = 2;
                        foreach (ExportQueryModel model in list)
                        {
                            IRow rowop10new = sheetop10.CreateRow(rows);
                            rowop10new.HeightInPoints = 20;
                            rowop10new.CreateCell(0).SetCellValue(model.No.ToString());
                            rowop10new.CreateCell(1).SetCellValue(model.PartCode);
                            rowop10new.CreateCell(2).SetCellValue(model.Bow);
                            rowop10new.CreateCell(3).SetCellValue(model.BowResult);
                            rowop10new.CreateCell(4).SetCellValue(model.BowPassageway);
                            rowop10new.CreateCell(5).SetCellValue(model.BowTesttime);
                            rowop10new.CreateCell(6).SetCellValue(model.DcCurrent);
                            rowop10new.CreateCell(7).SetCellValue(model.DcVoltage);
                            rowop10new.CreateCell(8).SetCellValue(model.ResultDc);
                            rowop10new.CreateCell(9).SetCellValue(model.AcCurrent);
                            rowop10new.CreateCell(10).SetCellValue(model.AcVoltage);
                            rowop10new.CreateCell(11).SetCellValue(model.ResultAc);
                            rowop10new.CreateCell(12).SetCellValue(model.PotPassageway);
                            rowop10new.CreateCell(13).SetCellValue(model.PotTesttime);
                            rowop10new.CreateCell(14).SetCellValue(model.TotalLength);//
                            rowop10new.CreateCell(15).SetCellValue(model.TotalLengthResult);
                            rowop10new.CreateCell(16).SetCellValue(model.BigPosVerticalValue);
                            rowop10new.CreateCell(17).SetCellValue(model.SmallPosVerticalValue);
                            rowop10new.CreateCell(18).SetCellValue(model.PotFinalJudge);
                            rowop10new.CreateCell(19).SetCellValue(model.Camber);
                            rowop10new.CreateCell(20).SetCellValue(model.CamberResult);
                            rowop10new.CreateCell(21).SetCellValue(model.FrontScallopPitch1);
                            rowop10new.CreateCell(22).SetCellValue(model.FrontScallopPitch2);
                            rowop10new.CreateCell(23).SetCellValue(model.FrontScallopDiameter1);
                            rowop10new.CreateCell(24).SetCellValue(model.FrontScallopDiameter2);
                            rowop10new.CreateCell(25).SetCellValue(model.BackScallopPitch1);
                            rowop10new.CreateCell(26).SetCellValue(model.BackScallopPitch2);
                            rowop10new.CreateCell(27).SetCellValue(model.BackScallopDiameter1);
                            rowop10new.CreateCell(28).SetCellValue(model.BackScallopDiameter2);
                            rowop10new.CreateCell(29).SetCellValue(model.FrontScallopPitchMax);
                            rowop10new.CreateCell(30).SetCellValue(model.FrontScallopPitchMin);
                            rowop10new.CreateCell(31).SetCellValue(model.BackScallopPitchMax);
                            rowop10new.CreateCell(32).SetCellValue(model.BackScallopPitchMin);
                            rowop10new.CreateCell(33).SetCellValue(model.FrontScallopDiameterMax);
                            rowop10new.CreateCell(34).SetCellValue(model.FrontScallopDiameterMin);
                            rowop10new.CreateCell(35).SetCellValue(model.BackScallopDiameterMax);
                            rowop10new.CreateCell(36).SetCellValue(model.BackScallopDiameterMin);
                            rowop10new.CreateCell(37).SetCellValue(model.FrontMinThickness);
                            rowop10new.CreateCell(38).SetCellValue(model.FrontMaxThickness);
                            //rowop10new.CreateCell(29).SetCellValue(model.BackThickness7);
                            rowop10new.CreateCell(39).SetCellValue(model.BackMinThickness);
                            rowop10new.CreateCell(40).SetCellValue(model.BackMaxThickness);
                            rowop10new.CreateCell(41).SetCellValue(model.FrontBarbLst1);
                            rowop10new.CreateCell(42).SetCellValue(model.FrontBarbLast);
                            rowop10new.CreateCell(43).SetCellValue(model.BackBarbLst1);
                            rowop10new.CreateCell(44).SetCellValue(model.BackBarbLast);
                            rowop10new.CreateCell(45).SetCellValue(model.CamberPassageway);
                            rowop10new.CreateCell(46).SetCellValue(model.CamberTesttime);
                            rowop10new.CreateCell(47).SetCellValue(model.Crfront1WaveDistanceResult);
                            rowop10new.CreateCell(48).SetCellValue(model.Crfront64WaveDistanceResult);
                            rowop10new.CreateCell(49).SetCellValue(model.CrlargeEndDistanceResult);
                            rowop10new.CreateCell(50).SetCellValue(model.CrsmallEndDistanceResult);
                            rowop10new.CreateCell(51).SetCellValue(model.CamberFinalJudge);
                            rows++;
                        }

                        ISheet titlelist = workbook.GetSheet("统计");
                        listCount = listCount.OrderBy(p => p.No).ToList();
                        int countrows = 2;
                        foreach (ExportQueryCount Countmodel in listCount)
                        {
                            IRow row = titlelist.CreateRow(countrows);
                            row.HeightInPoints = 20;
                            row.CreateCell(0).SetCellValue(Countmodel.DatatimeName);
                            row.CreateCell(1).SetCellValue(Countmodel.BowCount.ToString());
                            row.CreateCell(2).SetCellValue(Countmodel.BowOK.ToString());
                            row.CreateCell(3).SetCellValue(Countmodel.BowNG.ToString());
                            row.CreateCell(4).SetCellValue(Countmodel.hipotCount.ToString());
                            row.CreateCell(5).SetCellValue(Countmodel.hipotOK.ToString());
                            row.CreateCell(6).SetCellValue(Countmodel.hipotNG.ToString());
                            row.CreateCell(7).SetCellValue(Countmodel.CamberCount.ToString());
                            row.CreateCell(8).SetCellValue(Countmodel.CamberOK.ToString());
                            row.CreateCell(9).SetCellValue(Countmodel.CamberNG.ToString());
                            countrows++;
                        }
                        ISheet OeeSheet = workbook.GetSheet("OEE");
                        int Oeerows = 1;
                        foreach (PerformanceRecord RecordModel in ExportRecordList)
                        {
                            IRow row = OeeSheet.CreateRow(Oeerows);
                            row.HeightInPoints = 20;
                            row.CreateCell(0).SetCellValue(RecordModel.CreatedTime.Value.ToString("yyyy-MM-dd"));
                            row.CreateCell(1).SetCellValue(RecordModel.TotalRunTime.ToString());
                            if (RecordModel.StopTime < 0)
                            {
                                row.CreateCell(2).SetCellValue("0");
                            }
                            else
                            {
                                row.CreateCell(2).SetCellValue(RecordModel.StopTime.ToString());
                            }
                            if (RecordModel.WaitingTime < 0)
                            {
                                row.CreateCell(3).SetCellValue("0");
                            }
                            else
                            {
                                row.CreateCell(3).SetCellValue(RecordModel.WaitingTime.ToString());
                            }
                            if (RecordModel.FaultTime < 0)
                            {
                                row.CreateCell(4).SetCellValue("0");
                            }
                            else
                            {
                                row.CreateCell(4).SetCellValue(RecordModel.FaultTime.ToString());
                            }
                            row.CreateCell(5).SetCellValue(RecordModel.LoadTime.ToString());
                            row.CreateCell(6).SetCellValue(RecordModel.StartRate);
                            row.CreateCell(7).SetCellValue(RecordModel.Efficiency);
                            row.CreateCell(8).SetCellValue(RecordModel.ProductYield);
                            row.CreateCell(9).SetCellValue(RecordModel.OEE);
                            Oeerows++;
                        }
                        ISheet AlarmSheet = workbook.GetSheet("故障统计");
                        int Alarmrows = 1;
                        ExportAlarmList = ExportAlarmList.OrderByDescending(p => p.CreatedTime).ToList();
                        foreach (Equipmentfailurehistory AlarmModel in ExportAlarmList)
                        {
                            float a = 0;
                            a.ToString("p");
                            IRow row = AlarmSheet.CreateRow(Alarmrows);
                            row.HeightInPoints = 20;
                            if (AlarmModel.TotalTime != "0")
                            {
                                if (AlarmModel.CreatedTime != null)
                                {
                                    row.CreateCell(0).SetCellValue(AlarmModel.CreatedTime.Value.ToString("yyyy-MM-dd"));
                                }
                                if (AlarmModel.Content != null)
                                {
                                    row.CreateCell(1).SetCellValue(AlarmModel.Content);
                                }
                                if (AlarmModel.StartTime != null)
                                {
                                    row.CreateCell(2).SetCellValue(AlarmModel.StartTime.Value.ToString("HH:mm:ss"));
                                }
                                if (AlarmModel.FaultType == true || AlarmModel.EndTime == null || AlarmModel.TotalTime == null)
                                {
                                    row.CreateCell(3).SetCellValue("报警未消除");
                                }
                                else
                                {
                                    row.CreateCell(3).SetCellValue(AlarmModel.EndTime.Value.ToString("HH:mm:ss"));
                                    row.CreateCell(4).SetCellValue(float.Parse(AlarmModel.TotalTime) / 60);
                                }
                                Alarmrows++;
                            }
                        }

                        using (MemoryStream memoryStream = new MemoryStream())
                        {
                            workbook.Write(memoryStream);
                            data = memoryStream.ToArray();
                        }
                    }
                }
                else
                {
                    return MessageInfoUtils.Fail("No Data To Display!");
                }
                Stream stream = new MemoryStream(data);
                FileStreamResult fsr = File(stream, "application/vnd.ms-excel", fileName + ".xls");
                return MessageInfoUtils.Success(fsr);
            }
            catch (Exception ex)
            {
                return MessageInfoUtils.Fail("Error：" + ex.Message);
            }
        }

        public ResultReturn FileExportForHashtable(Hashtable ht, string sheetName, string fileName)
        {
            if (ht.Count > 0)
            {
                try
                {
                    byte[] data = null;
                    string excelFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "details.xls");
                    string newFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "details.xls");
                    if (System.IO.File.Exists(excelFilePath))
                    {
                        using (FileStream fileStream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
                        {
                            HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
                            ISheet sheetop10 = workbook.GetSheet("OP10");
                            ISheet sheetop20 = workbook.GetSheet("OP20");
                            ISheet sheetop30 = workbook.GetSheet("OP30");
                            foreach (DictionaryEntry entry in ht)
                            {
                                string key = entry.Key.ToString();
                                if (key == "OP10")
                                {
                                    List<Shape>? shapeslist = entry.Value as List<Shape>;
                                    if (shapeslist != null)
                                    {
                                        int one = 1;//从第二行开始
                                        foreach (Shape shapemodel in shapeslist)
                                        {
                                            if (shapemodel != null && shapemodel.detaillist.Count > 0)
                                            {
                                                List<Detectiondetail> detaillist = shapemodel.detaillist.OrderBy/*OrderByDescending*/(p =>
                                                {
                                                    if (string.IsNullOrEmpty(p.DetectedType))
                                                    {
                                                        return 0;
                                                    }
                                                    var match = Regex.Match(p.DetectedType, @"\d+");
                                                    if (match.Success)
                                                    {
                                                        return int.Parse(match.Value);
                                                    }
                                                    else
                                                    {
                                                        // 如果字符串中没有数字，默认返回一个较大的数值
                                                        return int.MaxValue;
                                                    }
                                                }).ToList();
                                                int cellrows = 0;
                                                NPOI.SS.UserModel.IRow rowop10new = sheetop10.CreateRow(one);
                                                rowop10new.CreateCell(cellrows).SetCellValue(one);
                                                rowop10new.CreateCell(1).SetCellValue(shapemodel.StationCode);
                                                rowop10new.CreateCell(2).SetCellValue(shapemodel.PartCode);
                                                rowop10new.CreateCell(3).SetCellValue(shapemodel.RunType);
                                                //4为水平拱曲度
                                                rowop10new.CreateCell(4).SetCellValue(detaillist.Where(p => p.DetectedType == "BOW").FirstOrDefault()?.DetectedData.ToString());
                                                rowop10new.CreateCell(5).SetCellValue(shapemodel.Result == true ? "OK" : "NG");
                                                rowop10new.CreateCell(6).SetCellValue(shapemodel.CreatedTime.ToString());
                                                rowop10new.CreateCell(7).SetCellValue(detaillist.Where(p => p.DetectedType == "BowMax").FirstOrDefault()?.DetectedData.ToString());
                                                rowop10new.CreateCell(8).SetCellValue(detaillist.Where(p => p.DetectedType == "BowMin").FirstOrDefault()?.DetectedData.ToString());
                                                cellrows = cellrows + 9;
                                                for (int j = 0; j < detaillist.Count; j++)
                                                {
                                                    ////排序水平拱曲度在在第一个，然后一次原始数据1-75
                                                    //Detectiondetail model = detaillist[j];
                                                    //if (model.DetectedType == "BOW")//水平拱曲度
                                                    //{
                                                    //    rowop10new.CreateCell(4).SetCellValue(model.DetectedData.ToString());
                                                    //}
                                                    //else
                                                    //{
                                                    //    //序号
                                                    //    rowop10new.CreateCell(cellrows + j).SetCellValue(model.DetectedData.ToString());
                                                    //}

                                                    //排序水平拱曲度在在第一个，然后一次原始数据1-75
                                                    Detectiondetail model = detaillist[j];
                                                    if (j < 82)
                                                    {
                                                        //序号
                                                        rowop10new.CreateCell(cellrows + j).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                }
                                            }
                                            one++;
                                        }
                                    }
                                }
                                if (key == "OP20")
                                {
                                    List<Voltagewithstand>? vllist = entry.Value as List<Voltagewithstand>;
                                    if (vllist != null)
                                    {
                                        int two = 1;
                                        foreach (var item in vllist)
                                        {
                                            NPOI.SS.UserModel.IRow rowop20new = sheetop20.CreateRow(two);
                                            rowop20new.CreateCell(0).SetCellValue(two);
                                            rowop20new.CreateCell(1).SetCellValue(item.StationCode);
                                            rowop20new.CreateCell(2).SetCellValue(item.PartCode);
                                            rowop20new.CreateCell(3).SetCellValue(item.RunType);

                                            if (item.detaillist != null && item.detaillist.Count > 0)
                                            {
                                                for (global::System.Int32 v = 0; v < item.detaillist.Count; v++)
                                                {
                                                    Detectiondetail model = item.detaillist[v];

                                                    if (model.DetectedType.Contains("DCv"))//DC设定电压
                                                    {
                                                        rowop20new.CreateCell(4).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                    else if (model.DetectedType.Contains("DCa"))//DC最终电流
                                                    {
                                                        rowop20new.CreateCell(5).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                    else if (model.DetectedType.Contains("ACv"))//AC设定电压
                                                    {
                                                        rowop20new.CreateCell(6).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                    else if (model.DetectedType.Contains("ACa"))//AC最终电流
                                                    {
                                                        rowop20new.CreateCell(7).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                    else if (model.DetectedType.Contains("BHV"))//大端垂直度
                                                    {
                                                        rowop20new.CreateCell(8).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                    else if (model.DetectedType.Contains("SHV"))//小端垂直度
                                                    {
                                                        rowop20new.CreateCell(9).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                    else if (model.DetectedType.Contains("TL"))//总长
                                                    {
                                                        rowop20new.CreateCell(10).SetCellValue(model.DetectedData.ToString());
                                                    }
                                                }
                                            }
                                            rowop20new.CreateCell(11).SetCellValue(item.Result == true ? "OK" : "NG");
                                            rowop20new.CreateCell(12).SetCellValue(item.CreatedTime.ToString());
                                            two++;
                                        }
                                    }
                                }
                                if (key == "OP30")
                                {
                                    List<Thicknessdetection>? tklist = entry.Value as List<Thicknessdetection>;
                                    if (tklist != null)
                                    {
                                        int k = 1;
                                        foreach (var item in tklist)
                                        {
                                            NPOI.SS.UserModel.IRow rowop30new = sheetop30.CreateRow(k);
                                            rowop30new.CreateCell(0).SetCellValue(k);
                                            rowop30new.CreateCell(1).SetCellValue(item.StationCode);
                                            rowop30new.CreateCell(2).SetCellValue(item.PartCode);
                                            rowop30new.CreateCell(3).SetCellValue(item.RunType);
                                            //4立面拱曲度
                                            rowop30new.CreateCell(5).SetCellValue(item.FacadeCurvatureResult == true ? "OK" : "NG");
                                            rowop30new.CreateCell(6).SetCellValue(item.FrontThicknessResult1 == true ? "OK" : "NG");
                                            rowop30new.CreateCell(7).SetCellValue(item._3dscannerResult == true ? "OK" : "NG");
                                            rowop30new.CreateCell(8).SetCellValue(item.Result == true ? "OK" : "NG");
                                            rowop30new.CreateCell(9).SetCellValue(item.CreatedTime.ToString());
                                            if (item.detaillist != null && item.detaillist.Count > 0)
                                            {
                                                List<Detectiondetail> FrontList = item.detaillist.Where(p => p.DetectedTypeDisplay == "FCT").ToList();//正面厚度
                                                List<Detectiondetail> BackList = item.detaillist.Where(p => p.DetectedTypeDisplay == "BCT").ToList();//背面厚度
                                                List<Detectiondetail> FacadeList = item.detaillist.Where(p => p.DetectedTypeDisplay == "Cam").ToList();
                                                //rowop30new.CreateCell(4).SetCellValue(FacadeList[0].DetectedData.ToString());

                                                if (FacadeList.Count != 0)
                                                {
                                                    rowop30new.CreateCell(4).SetCellValue(FacadeList[0].DetectedData.ToString());
                                                }
                                                else
                                                {
                                                    rowop30new.CreateCell(4).SetCellValue("NULL");
                                                }

                                                FrontList = FrontList.OrderBy(p =>
                                                {
                                                    if (string.IsNullOrEmpty(p.DetectedType))
                                                    {
                                                        return 0;
                                                    }
                                                    var match = Regex.Match(p.DetectedType, @"\d+");
                                                    if (match.Success)
                                                    {
                                                        return int.Parse(match.Value);
                                                    }
                                                    else
                                                    {
                                                        // 如果字符串中没有数字，默认返回一个较大的数值，确保没有数字的字符串排在后面
                                                        return int.MaxValue;
                                                    }
                                                }).ToList();
                                                BackList = BackList.OrderBy(p =>
                                                {
                                                    if (string.IsNullOrEmpty(p.DetectedType))
                                                    {
                                                        return 0;
                                                    }
                                                    var match = Regex.Match(p.DetectedType, @"\d+");
                                                    if (match.Success)
                                                    {
                                                        return int.Parse(match.Value);
                                                    }
                                                    else
                                                    {
                                                        // 如果字符串中没有数字，默认返回一个较大的数值，确保没有数字的字符串排在后面
                                                        return int.MaxValue;
                                                    }
                                                }).ToList();
                                                int cellrow = 10;
                                                for (int a = 0; a < FrontList.Count; a++)
                                                {
                                                    rowop30new.CreateCell(cellrow + a).SetCellValue(FrontList[a].DetectedData.ToString());
                                                }
                                                cellrow = 18;
                                                for (int b = 0; b < BackList.Count; b++)
                                                {
                                                    rowop30new.CreateCell(cellrow + b).SetCellValue(BackList[b].DetectedData.ToString());
                                                }
                                                k++;
                                            }
                                        }
                                    }
                                }
                            }

                            using (MemoryStream memoryStream = new MemoryStream())
                            {
                                workbook.Write(memoryStream);
                                data = memoryStream.ToArray();
                            }
                        }
                    }
                    else
                    {
                        return MessageInfoUtils.Fail("No Data To Display!");
                    }

                    //using (MemoryStream ms = new MemoryStream())
                    //{
                    //    hSSFworkbook.Write(ms);
                    //    ms.Flush();
                    //    data = ms.ToArray();
                    //}
                    Stream stream = new MemoryStream(data);
                    FileStreamResult fsr = File(stream, "application/vnd.ms-excel", fileName + ".xls");
                    return MessageInfoUtils.Success(fsr);
                }
                catch (Exception ex)
                {
                    return MessageInfoUtils.Fail("Error：" + ex.Message);
                }
            }
            else
            {
                return MessageInfoUtils.Fail("No Data To Display!");
            }
        }

        /// <summary>
        /// 生成文件流
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="sheetName"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public FileStreamResult GetFileStream(Stream stream, string streamType, string fileName)
        {
            FileStreamResult fsr = File(stream, streamType, fileName);
            return fsr;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="stream">文件流</param>
        /// <param name="handIndex">标题所在得行数</param>
        /// <param name="skipIndex">需要跳过多少行，包含当前行</param>
        /// <param name="specialIndex">需要特殊提取得单元格</param>
        /// <returns></returns>
        public List<ReturnKeyValueList> FileUpload(Stream stream, int handIndex = 0, int skipIndex = 0, List<SpecialModel>? specialIndex = null)
        {
            List<dynamic> list = new List<dynamic>();
            ISheet? sheet = null;
            bool isRead = false;
            if (POIFSFileSystem.HasPOIFSHeader(stream))
            {
                var workbook = new HSSFWorkbook(stream);
                sheet = workbook.GetSheetAt(0);
                isRead = true;
            }
            if (!isRead && POIXMLDocument.HasOOXMLHeader(stream))
            {
                var workbook = new XSSFWorkbook(stream);
                sheet = workbook.GetSheetAt(0);
            }
            List<ReturnKeyValueList> InfoList = new List<ReturnKeyValueList>();
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                var info = new ReturnKeyValueList();
                if (specialIndex != null)
                {
                    var specialInfo = specialIndex.FirstOrDefault(p => p.RowIndex == i);
                    if (specialInfo != null)
                    {
                        string name = specialInfo.ColumnName;
                        int RowIndex = specialInfo.RowIndex;
                        int ColumnIndex = specialInfo.ColumnIndex;
                        var specialCellInfo = new ReturnKeyValue();
                        specialCellInfo.Name = name;
                        specialCellInfo.Key = name;
                        specialCellInfo.Display = true;
                        if (sheet.GetRow(RowIndex).GetCell(ColumnIndex).CellType == CellType.String)
                            specialCellInfo.Value = sheet.GetRow(RowIndex).GetCell(ColumnIndex).StringCellValue.ToString();
                        else if (sheet.GetRow(RowIndex).GetCell(ColumnIndex).CellType == CellType.Numeric)
                        {
                            if (DateUtil.IsCellDateFormatted(sheet.GetRow(RowIndex).GetCell(ColumnIndex)))
                            {
                                specialCellInfo.Value = sheet.GetRow(RowIndex).GetCell(ColumnIndex).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            else
                            {
                                specialCellInfo.Value = sheet.GetRow(RowIndex).GetCell(ColumnIndex).NumericCellValue.ToString();
                            }
                        }
                        else
                            specialCellInfo.Value = sheet.GetRow(RowIndex).GetCell(ColumnIndex).DateCellValue.ToString();
                        info.values.Add(specialCellInfo);
                    }
                }
                if (i <= skipIndex)
                    continue;
                var item = sheet.GetRow(i);
                if (item == null)
                    continue;
                for (int b = 0; b < item.LastCellNum; b++)
                {
                    if (item.GetCell(b) == null || item.GetCell(b).CellType == CellType.Blank)
                        continue;

                    var cellInfo = new ReturnKeyValue();
                    cellInfo.Name = sheet.GetRow(handIndex).GetCell(b).StringCellValue.ToString();
                    cellInfo.Key = sheet.GetRow(handIndex).GetCell(b).StringCellValue.ToString();
                    cellInfo.Display = true;
                    if (item.GetCell(b).CellType == CellType.String)
                        cellInfo.Value = item.GetCell(b).StringCellValue.ToString();
                    else if (item.GetCell(b).CellType == CellType.Numeric)
                    {
                        if (DateUtil.IsCellDateFormatted(item.GetCell(b)))
                        {
                            cellInfo.Value = item.GetCell(b).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        else
                        {
                            cellInfo.Value = item.GetCell(b).NumericCellValue.ToString();
                        }
                    }
                    else if (item.GetCell(b).CellType == CellType.Boolean)
                        cellInfo.Value = item.GetCell(b).BooleanCellValue.ToString();
                    else
                        cellInfo.Value = item.GetCell(b).DateCellValue.ToString();
                    info.values.Add(cellInfo);
                }
                if (info.values.Count > 0)
                    InfoList.Add(info);
            }
            return InfoList;
        }

        /// <summary>
        /// 读取txt文件，并返回文件中的内容
        /// </summary>
        /// <returns>txt文件内容</returns>
        public string ReadTxTContent(Stream stream)
        {
            try
            {
                string s_con = string.Empty;
                // 创建一个 StreamReader 的实例来读取文件 using 语句也能关闭 StreamReader
                using (StreamReader sr = new StreamReader(stream, Encoding.Default))
                {
                    string line;
                    // 从文件读取并显示行，直到文件的末尾
                    while ((line = sr.ReadLine()) != null)
                    {
                        s_con += line;
                    }
                }
                return s_con;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        /// <summary>
        /// 压缩成zip
        /// </summary>
        /// <param name="filesPath">d:\a.xlsx</param>
        /// <param name="filesPath2">d:\a.xlsx</param>
        /// <param name="zipFilePath">d:\a.zip</param>
        public void CreateZipFileOne(string filesPath, string filesPath2, string zipFilePath)
        {
            try
            {
                using (ZipOutputStream s = new ZipOutputStream(System.IO.File.Create(zipFilePath)))
                {
                    s.SetLevel(9); // 压缩级别 0-9
                                   //s.Password = "123"; //Zip压缩文件密码
                    byte[] buffer = new byte[4096]; //缓冲区大小

                    if (!string.IsNullOrEmpty(filesPath))
                    {
                        ZipEntry entry = new ZipEntry(Path.GetFileName(filesPath));
                        entry.DateTime = DateTime.Now;
                        s.PutNextEntry(entry);
                        using (FileStream fs = System.IO.File.OpenRead(filesPath))
                        {
                            int sourceBytes;
                            do
                            {
                                sourceBytes = fs.Read(buffer, 0, buffer.Length);
                                s.Write(buffer, 0, sourceBytes);
                            } while (sourceBytes > 0);
                        }
                    }
                    if (!string.IsNullOrEmpty(filesPath2))
                    {
                        ZipEntry entry2 = new ZipEntry(Path.GetFileName(filesPath2));
                        entry2.DateTime = DateTime.Now;
                        s.PutNextEntry(entry2);
                        using (FileStream fs2 = System.IO.File.OpenRead(filesPath2))
                        {
                            int sourceBytes;
                            do
                            {
                                sourceBytes = fs2.Read(buffer, 0, buffer.Length);
                                s.Write(buffer, 0, sourceBytes);
                            } while (sourceBytes > 0);
                        }
                    }
                    s.Finish();
                    s.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception during processing {0}", ex);
            }
        }
    }
}